﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_REF_TOUR_Get_Search_Count')
	BEGIN
		DROP Procedure usp_UPDMS_REF_TOUR_Get_Search_Count
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_REF_TOUR_Get_Search_Count
**	Desc : 기행기의 검색결과수를 카운트한다.
**	Test Exec Query : Exec usp_UPDMS_REF_TOUR_Get_Search_Count
**	Called by : Ref_Dac_UPDMS_REF_TOUR.cs
**	Program ID : Ref1012m
**	Auth : 송시명
**	Date : 2013-04-17
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**			
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_REF_TOUR_Get_Search_Count]
@ls_shop_id nvarchar(max),
@ls_nick_nm nvarchar(200),
@ls_tour_dt_from nvarchar(10),
@ls_tour_dt_to nvarchar(10),
@ls_homerun_yn nvarchar(5),
@ls_satisfaction nvarchar(3),
@ls_contents nvarchar(max),
@ls_keyword nvarchar(2000),
@ls_file_exist varchar(1)

AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @shop_ids nvarchar(max)
IF @ls_shop_id = ''
BEGIN
    SET @shop_ids = '%'
END
ELSE
BEGIN
    SET @shop_ids = @ls_shop_id
END

--첨부파일존재여부
DECLARE @file_exist int
IF @ls_file_exist = ''
BEGIN
    SET @file_exist = 0
END
ELSE
BEGIN
    SET @file_exist = 1
END

SELECT COUNT(urt.Seq) AS Search_Count
  FROM UPDMS_REF_TOUR AS urt WITH(NOLOCK)
  JOIN UPDMS_REF_SHOP AS urs WITH(NOLOCK) ON urt.Shop_Id = urs.Shop_Id
 WHERE (urt.Shop_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@shop_ids))
       OR urt.Shop_Id LIKE @shop_ids
       )
   AND urt.Nick_Nm LIKE '%' + @ls_nick_nm + '%'
   AND urt.Tour_Dt >= @ls_tour_dt_from
   AND urt.Tour_Dt <= @ls_tour_dt_to
   AND urt.HomeRun_Yn LIKE @ls_homerun_yn + '%'
   AND urt.Satisfaction >= @ls_satisfaction
   AND urt.Contents LIKE '%' + @ls_contents + '%'
   AND urt.Keyword LIKE '%' + @ls_keyword + '%'
   AND dbo.ufn_UPDMS_Get_Exist_Attach_File(urt.Seq, 'Ref1012m') >= @file_exist
GO